﻿/* Author: Danny Beaumont
 * this program will send to multiple clients if the ROW is selected.
 */

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;
using System.IO;
using System.Net;
using System.Net.Sockets;
using System.Net.Mail;

namespace HolidayMailer
{
    public partial class Form1 : Form
    {

        SQLiteConnection sqlconnection;
        SQLiteCommand insertCommand;
        SQLiteCommand createCommand;
        SQLiteCommand tableBuilder;
        SQLiteCommand countCommand;
        SQLiteCommand purgeCommand;
        SQLiteCommand filterCommand;
        SQLiteDataAdapter customerAdpter;
        SQLiteDataReader countReader;
        DataSet customerSet;
        DataTable customerTable;
        bool Attachment;

        SmtpClient SMTP;
        public Form1()
        {
            Control.CheckForIllegalCrossThreadCalls = false;

            // create a new database connection:
            if (File.Exists("database.db"))
                sqlconnection = new SQLiteConnection("Data Source=database.db;Version=3;New=False;Compress=True;");
            else
            {
                sqlconnection = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;");
                // open the connection:
                sqlconnection.Open();

                // create a new SQL command:
                createCommand = sqlconnection.CreateCommand();
                createCommand.CommandText = "CREATE TABLE customer (CustID integer primary key, firstName varchar(30),lastName varchar(30),email varchar(30),sent varchar(15));";
                createCommand.ExecuteNonQuery();
                sqlconnection.Close();

            }
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

            loadTable();

        }
        private void customerGrid_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
        {
            customerGrid.Rows[e.RowIndex].ErrorText = "";
            

            if (customerGrid.Rows[e.RowIndex].IsNewRow) return;

            int indexNumber = int.Parse(customerGrid.Rows[e.RowIndex].Cells[0].ToString());// customer number
            string given = customerGrid.Rows[e.RowIndex].Cells[1].ToString();// first name
            string surname = customerGrid.Rows[e.RowIndex].Cells[2].ToString();// last name
            string email = customerGrid.Rows[e.RowIndex].Cells[3].ToString();// email address
            DateTime lastSent = DateTime.Parse(customerGrid.Rows[e.RowIndex].Cells[3].ToString());// year
            
        }

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Environment.Exit(0);
        }

        private void addButton_Click(object sender, EventArgs e)
        {

            int indexer =0;
            sqlconnection.Open();
            //count our existing rows first
            countCommand = sqlconnection.CreateCommand();
            countCommand.CommandText = "SELECT COUNT(*) FROM customer;";
            countReader = countCommand.ExecuteReader();
            if (countReader.Read())
            {
                indexer = int.Parse(countReader[0].ToString());
                countReader.Close();
            }
           // DateTime date = DateTime.ParseExact(sentBox.Text.ToString(), "yyyy-MM-dd", null);
            //then do our insert command
            insertCommand = sqlconnection.CreateCommand();
            insertCommand.CommandText = "INSERT INTO customer (CustID,firstName,lastName,email,sent) VALUES (" + (indexer + 1) + ",\'" +
                firstBox.Text.ToString() + "\',\'" + lastBox.Text.ToString() + "\',\'" + emailBox.Text.ToString() + "\',\'" +sentBox.Text.ToString()+ "\');";
            insertCommand.ExecuteNonQuery();
            sqlconnection.Close();
            resetAddTexts();
            loadTable();
        }

        private void discardButton_Click(object sender, EventArgs e)
        {
            resetAddTexts();
        }
        private void resetAddTexts()
        {
            firstBox.ResetText();
            lastBox.ResetText();
            emailBox.ResetText();
            sentBox.ResetText();
        }
        private void loadTable()
        {
            sqlconnection.Open();
            tableBuilder = sqlconnection.CreateCommand();
            tableBuilder.CommandText = "SELECT * FROM customer;";
            using (customerAdpter = new SQLiteDataAdapter(tableBuilder))
            {
                customerSet = new DataSet();
                //try
                //{
                    customerAdpter.Fill(customerSet);

                    customerTable = new DataTable();
                    customerAdpter.Fill(customerTable);
                    this.customerGrid.DataSource = customerTable;
                //}
                //catch (Exception e)
                //{
                //}

            }
            sqlconnection.Close();
        }

        private void purgeButton_Click(object sender, EventArgs e)
        {
            sqlconnection.Open();
            purgeCommand = sqlconnection.CreateCommand();
            purgeCommand.CommandText = "DELETE FROM customer WHERE CustID =" + purgeBox.Text.ToString() + ";";
            purgeCommand.ExecuteNonQuery();
            sqlconnection.Close();
            loadTable();
        }

        private void filterButton_Click(object sender, EventArgs e)
        {
            sqlconnection.Open();
            filterCommand = sqlconnection.CreateCommand();
            filterCommand.CommandText = "SELECT * FROM customer WHERE lastName LIKE \'" + letterBox.Text.ToString().ToUpper() + "%\';";
            using (customerAdpter = new SQLiteDataAdapter(filterCommand))
            {
                customerSet = new DataSet();
                //try
                //{
                customerAdpter.Fill(customerSet);

                customerTable = new DataTable();
                customerAdpter.Fill(customerTable);
                this.customerGrid.DataSource = customerTable;
                //}
                //catch (Exception e)
                //{
                //}

            }
            sqlconnection.Close();

        }

        private void optionsToolStripMenuItem_Click(object sender, EventArgs e)
        {
            
        }

        private void sendButton_Click(object sender, EventArgs e)
        {

            int selectedRowCount = customerGrid.Rows.GetRowCount(DataGridViewElementStates.Selected);
            if (selectedRowCount > 0)
            {
                for (int i = 0; i < selectedRowCount; i++)
                {
                    
                    SMTP = new SmtpClient(); // ssl smtp server to reflect today's email servers
                    string name = customerGrid.SelectedRows[i].Cells[3].FormattedValue.ToString();
                    MailAddress fromAddress = new MailAddress(fromBox.Text);
                    MailAddress toAddress = new MailAddress(name);
                    MailMessage message = new MailMessage();// sending to myself for testing
                    message.From = fromAddress;
                    message.To.Add(toAddress);
                    message.Subject = subjectBox.Text;
                    message.Body = contextBox.Text;
                    if (Attachment)
                    {
                        Attachment attach = new Attachment(attachBox.Text);
                        message.Attachments.Add(attach);
                    }
                    NetworkCredential streetCred = new NetworkCredential(fromAddress.Address, passwordBox.Text);

                    SMTP.Host = serverBox.Text;
                    SMTP.Port = int.Parse(portBox.Text);
                    SMTP.UseDefaultCredentials = false;
                    SMTP.Credentials = streetCred;

                    //SMTP.EnableSsl = true;
                    SMTP.DeliveryMethod = SmtpDeliveryMethod.Network;
                    try
                    {
                        SMTP.Send(message);
                    }
                    catch (Exception exc)
                    {
                    }
                    //DateTime date = DateTime.Now;
                    //sqlconnection.Open();
                    //SQLiteCommand updateCommand = sqlconnection.CreateCommand();
                    //updateCommand.CommandText = "UPDATE customer SET sent = " + date.ToShortDateString() + " WHERE email = \'" + toAddress.Address + "\';";
                    //updateCommand.ExecuteNonQuery();
                    //sqlconnection.Close();
                }
            }
            
        }

        private void button1_Click(object sender, EventArgs e)
        {

            if (attachBox.Text.Length == 0 || !Directory.Exists(attachBox.Text))
            {
                attachBox.Text = "ooops try again";

            }
            else
            {
                Attachment = true;
            }
        }
        
       
    }
}
